﻿Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient


Imports System.Runtime.InteropServices.Marshal
Imports System.Web
Imports System.Security.Principal
Imports System.Runtime.InteropServices
Imports System.Configuration
Imports System

Partial Class Paginas_Carga_Masiva_Productos2
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub proceso_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles proceso.Click
        Dim strscript As String
        Dim Rutinas As New DataServices.Rutinas
        Dim scriptblock1 As String
        Dim Destino As String

        If IsNothing(MyFile.PostedFile) Then
            Dim scriptblock As String = "<script language='javascript'>alert('Seleccione un archivo');</script>"
            Page.RegisterStartupScript("Launch_Report_Window", scriptblock)
            Exit Sub
        End If

        If MyFile.PostedFile.ContentLength = 0 Then
            Dim scriptblock As String = "<script language='javascript'>alert('No se cargó, archivo vacío ');</script>"
            Page.RegisterStartupScript("Launch_Report_Window", scriptblock)
            Exit Sub
        End If

        Dim nombreF As String = Path.GetFileName(MyFile.PostedFile.FileName)
        Dim nFile As String = Mid(nombreF, 1, Len(nombreF) - 4)
        Dim tFile As String = Mid(nombreF, Len(nombreF) - 3)
        If UCase(tFile) <> ".XLS" Then
            Destino = "alert('Tipo del archivo errado. Debe ser .XLS');"
            scriptblock1 = "<script language='javascript'>" & Destino & "</script>"
            Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
            Exit Sub
        End If
        Dim currentTime As System.DateTime = System.DateTime.Now
        Dim dfecha_1 As String = "_" & currentTime.Year & currentTime.Month & currentTime.Day & "_" & currentTime.Hour & currentTime.Minute & currentTime.Second
        Destino = Server.MapPath(Nothing) + "\Upload\" + nFile + dfecha_1 + tFile

        MyFile.PostedFile.SaveAs(Destino)
        Dim hoja() As String = GetExcelSheetNames(Destino)


        Destino = excelToSql(Destino, hoja(0), "UploadProductos", 1)
        'System.IO.File.Delete(Destino)
        Destino = "alert('" & Destino & "');"
        scriptblock1 = "<script language='javascript'>" & Destino & "</script>"
        Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)

    End Sub


    Function excelToSql(ByVal FileName As String, ByVal SheetName As String, ByVal TableName As String, ByVal SP As Integer) As String
        excelToSql = ""

        Dim Rutinas As New DataServices.Rutinas
        Dim j As Integer
        Dim mensajeBD As String
        Dim scriptblock1 As String
        Dim destino As String
        Dim Dias As Integer
        xglosa.Text = ""

        Try


            Dim LISTA_BD As String
            LISTA_BD = "CóDIGO,código clínica,NOMBRE,COMPRA PROMEDIO MENSUAL,UNIDAD,PRECIO PROMEDIO,PROVEEDOR FRECUENTE"


            Dim SQL As String = ConfigurationSettings.AppSettings("ConnectionString")
            Dim cmdDatabase As SqlCommand
            Dim contoSql As SqlConnection
            Dim contoExcel As OleDbConnection
            Dim cmd As OleDbCommand
            Dim Excel As String = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";Extended Properties=""Excel 12.0;HDR=YES"";"

            contoExcel = New OleDbConnection(Excel)
            contoExcel.Open()
            mensajeBD = ""
            cmd = New OleDbCommand("SELECT * FROM [" & SheetName.ToString & "]", contoExcel)
            Dim rdr As OleDbDataReader = cmd.ExecuteReader()


            'Dim SQL As String = ConfigurationSettings.AppSettings("ConnectionString")
            'Dim cmdDatabase As SqlCommand
            'Dim contoSql As SqlConnection
            'Dim contoExcel As OleDbConnection
            'Dim cmd As OleDbCommand
            'Dim Excel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""

            'contoExcel = New OleDbConnection(Excel)
            'contoExcel.Open()
            'mensajeBD = ""
            'cmd = New OleDbCommand("SELECT * FROM [" & SheetName.ToString & "]", contoExcel)
            'Dim rdr As OleDbDataReader = cmd.ExecuteReader()
            Dim schema As DataTable = rdr.GetSchemaTable
            Dim name As String = ""

            Dim Lista_Col As String = ""
            For Each row As DataRow In schema.Rows
                Dim i As Integer = 0
                For Each col As DataColumn In schema.Columns

                    If i = 0 Then
                        Lista_Col = Lista_Col & Convert.ToString(row(0)) & ","
                        name = name & "[" & Convert.ToString(row(0)) & "] nvarchar(255), "
                    End If
                    i = i + 1
                Next
            Next

            name = Mid(name, 1, Len(name) - 2)
            Lista_Col = Mid(Lista_Col, 1, Len(Lista_Col) - 1)
            Dim LISTA_BD_r() As String = LISTA_BD.Split(",")
            Dim Lista_Col_r() As String = Lista_Col.Split(",")

            Dim k As Integer
            Dim esta As Integer = 0
            Dim tit_aux As String = ""
            Dim mensaje As String = ""

            For k = 0 To UBound(LISTA_BD_r)
                tit_aux = LISTA_BD_r(k)
                For j = 0 To UBound(Lista_Col_r)
                    If UCase(LISTA_BD_r(k)) = UCase(Lista_Col_r(j)) Then
                        tit_aux = ""
                    End If
                Next
                If tit_aux <> "" Then
                    mensaje = mensaje & " , " & tit_aux
                End If
            Next

            If mensaje <> "" Then
                contoExcel.Close()
                mensaje = "LAS SIGUIENTES COLUMNAS FALTAN O EL NOMBRE EN LA PLANILLA ES INCORRECTO : " & mensaje
                destino = "alert('" & mensaje & "');"
                scriptblock1 = "<script language='javascript'>" & destino & "</script>"
                Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
                contoExcel.Close()
                contoExcel.Dispose()
                Exit Function

            End If

            Dim comandoSql As String
            comandoSql = "IF object_id('" & TableName & "') IS NOT NULL BEGIN    DROP TABLE " & TableName & " END "
            comandoSql = comandoSql & "Create table " & TableName & " (" & name & ")"
            contoSql = New SqlConnection(SQL)
            cmdDatabase = New SqlCommand(comandoSql, contoSql)
            contoSql.Open()
            cmdDatabase.ExecuteNonQuery()

            Using bulkCopy As New SqlBulkCopy(contoSql)
                bulkCopy.DestinationTableName = TableName
                bulkCopy.BulkCopyTimeout = 60
                bulkCopy.WriteToServer(rdr)
            End Using

            Dim Csql As String

            Csql = " "




            grilla.Visible = False


            'Csql = " Select [RUT] + '-->' + convert(varchar(20),[PROVEEDOR FRECUENTE]) as campo1 from UploadProductos where DBO.VALIDADV([PROVEEDOR FRECUENTE])=1 "

            'If Busca(Csql, "RUT Invalidos") = 1 Then
            '    xglosa.Text = "En la planilla hay  RUT no validos . Los RUT invalidos fueron excluidos del proceso.  "
            '    excelToSql = xglosa.Text
            '    grilla.Visible = True
            '    'contoExcel.Close()
            '    'contoExcel.Dispose()
            '    'Exit Function
            'End If




            If SP = 1 Then
                Dim parametros(1) As Object
                parametros(0) = Session("empresa")
                parametros(1) = 2
                Dim Rp As New ResponseValidator("", "")
                Rp.ExecuteDataset("SP_" & TableName, parametros)

                If Not Rp.Errores Then
                    Rp.Commit()
                Else
                    mensajeBD = Rp.Mensaje
                End If
                Rp.Dispose()
            End If

            contoSql.Close()
            contoExcel.Close()
            contoExcel.Dispose()
            If mensajeBD = "" Then
                excelToSql = "Datos Transferidos"
            Else
                excelToSql = mensajeBD
            End If
        Catch ex As Exception
            excelToSql = ex.Message
        End Try

    End Function

    Public Function GetExcelSheetNames(ByVal excelFileName As String) As String()
        Dim con As OleDbConnection = Nothing
        Dim dt As DataTable = Nothing

        'Dim conStr As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + excelFileName & ";Extended Properties=Excel 8.0;"
        'con = New OleDbConnection(conStr)
        'con.Open()

        Dim Excel As String = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelFileName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
        con = New OleDbConnection(Excel)
        con.Open()


        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt Is Nothing Then
            Return Nothing
        End If
        Dim excelSheetNames As String() = New String(dt.Rows.Count - 1) {}
        Dim i As Integer = 0
        For Each row As DataRow In dt.Rows
            excelSheetNames(i) = row("TABLE_NAME").ToString()
            i += 1
        Next
        con.Close()
        con.Dispose()
        Return excelSheetNames
    End Function

    Function Busca(ByVal csql As String, ByVal titulo As String) As Integer
        Busca = 0
        Dim resultado As New DataSet
        Dim Rutinas As New DataServices.Rutinas

        resultado = Rutinas.ExtraeSql(csql)
        If resultado.Tables.Count <> 0 Then
            Call Rutinas.Grilla_Nolink(resultado.Tables(0), grilla, Titulo)
            grilla.Visible = True
            Busca = 1
        End If
    End Function

    Protected Sub excel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles excel.Click

        Dim csql As String

        Csql = " Select [RUT] + '-->' + convert(varchar(10),vendedor) as campo1 from UploadRutConvenio where DBO.VALIDADV([RUT])=1 "

        If Busca(Csql, "RUT Invalidos") = 1 Then
            xglosa.Text = "En la planilla hay  RUT no validos . Los RUT invalidos fueron excluidos del proceso.  "
            'xglosa.Text = xglosa.Text
            grilla.Visible = True
            'contoExcel.Close()
            'contoExcel.Dispose()
            'Exit Function
        End If


        Dim exp As New DataServices.Utilidades.ExportarExcel
        exp.Exportar(Me.grilla, Response)
        exp.Dispose()

    End Sub
End Class
